import logging
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
import openpyxl
import datetime
import os
import sys
import configparser
import ChengBanBuMen
from hanchlogger import logger
import hanchxl

from BuMenChengJi import buMenChengJi
from tkinter import simpledialog

from openpyxl.cell import MergedCell
timeCur = datetime.datetime.now()

dirPath = 'E:\\日常工作\\接诉即办排名情况\\2023\\季度测试'
# dirPath = 'E:\\日常工作\\接诉即办排名情况\\2023\\第二季度\\5月'
# dirPath = 'E:\\日常工作\\11月\\14\\每日必办\\得分统计表\\{}月'.format(numMonth)
# dirPath = os.path.dirname(os.path.realpath(sys.executable))

logFile = dirPath + '\\JieDaoPaiMing.log'
logger.get_log(logFile, 'LoggerJieDaoPaiMing', logging.DEBUG)

iniFilePathCur = dirPath + '\\autoUnresolvedList.ini'
iniConfig = configparser.ConfigParser(allow_no_value=True)
iniConfig.read(iniFilePathCur, encoding='utf-8')

fileJieDan = dirPath + '\\工单明细表-接单.xlsx'
fileKaoHe = dirPath + '\\工单明细表-考核.xlsx'
fileKaoHePiPei = dirPath + '\\工单明细表-考核-已匹配承办部门.xlsx'
fileJieDanPiPei = dirPath + '\\工单明细表-接单-已匹配承办部门.xlsx'
fileJieDanNo = dirPath + '\\工单明细表-接单-未处理.xlsx'
fileKaoHeNo = dirPath + '\\工单明细表-考核-未处理.xlsx'
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileResultSave = dirPath + '\\拱辰街道接诉即办月度排名.xlsx'
fileResultMuBan = dirPath + '\\模板-月度得分统计.xlsx'
fileColIndexInfo = dirPath + '\\数据列索引信息.xlsx'
fileChengBanReplace = dirPath + '\\需替换部门.xlsx'
fileQuanBu = dirPath + '\\全部工单.xlsx'

# 打开工单明细表-考核.xlsx
bookKaoHe = openpyxl.load_workbook(fileKaoHe, data_only=True)
sheetKaoHe = bookKaoHe.active
# sheetKaoHe = bookKaoHe['考核工单']
maxRowKaoHe = sheetKaoHe.max_row
maxColKaoHe = sheetKaoHe.max_column
logger.debug('工单明细表-考核.xlsx -------行数：{}行    列数：{}列'.format(maxRowKaoHe, maxColKaoHe))

# 打开 工单明细表-接单.xlsx
bookJieDan = openpyxl.load_workbook(fileJieDan, data_only=True)
sheetJieDan = bookJieDan.active
# sheetJieDan = bookJieDan['接单情况']
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('工单明细表-接单.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))

# 打开替换部门字典表
logger.debug('打开替换部门字典表 文件：{}'.format(fileChengBanReplace))
replaceBuMen = ReplaceBuMen.ReplaceDirectory()
replaceBuMen.load_directory(fileChengBanReplace)
listReplaceBuMen = replaceBuMen.get_replace_directory()

# 打开 全部工单.xlsx
bookQuanBu = openpyxl.load_workbook(fileQuanBu, data_only=True)
sheetQuanBu = bookQuanBu.active
maxRowQuanBu = sheetQuanBu.max_row
maxColQuanBu = sheetQuanBu.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowQuanBu, maxColQuanBu))

colCode = hanchxl.get_col_index(sheetJieDan, '工单编号')
sheetJieDan.insert_cols(colCode + 1, 3)
sheetJieDan.cell(1, colCode + 1).value = '承办部门'
sheetJieDan.cell(1, colCode + 2).value = '参与部门'
sheetJieDan.cell(1, colCode + 3).value = '是否处理'

hanchxl.pi_pei_te_ding_col(sheetJieDan, '工单编号', sheetQuanBu, '承办部门', '参与部门')
for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetKaoHe, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDan, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDan, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetKaoHe, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
hanchxl.remove_repeat_value(sheetJieDan, '承办部门', '参与部门')

# 打开 数据列索引信息.xlsx
bookColIndex = openpyxl.load_workbook(fileColIndexInfo, data_only=True)
sheetColIndexKeShi = bookColIndex['科室']
maxRowColIndexKeShi = sheetColIndexKeShi.max_row
maxColColIndexKeShi = sheetColIndexKeShi.max_column
logger.debug('数据列索引信息.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowColIndexKeShi, maxColColIndexKeShi))
listColKeshi = {}
for rowIndex in range(2, maxRowColIndexKeShi + 1):
    valueKeShiName = sheetColIndexKeShi.cell(rowIndex, 1).value
    valueKeShiIndex = sheetColIndexKeShi.cell(rowIndex, 2).value
    if valueKeShiName is not None and valueKeShiName != '':
        listColKeshi[valueKeShiName] = valueKeShiIndex
logger.debug('{}'.format(listColKeshi))

sheetColIndexSheQu = bookColIndex['社区村']
maxRowColIndexSheQu = sheetColIndexSheQu.max_row
maxColColIndexSheQu = sheetColIndexSheQu.max_column
logger.debug('数据列索引信息.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowColIndexSheQu, maxColColIndexSheQu))
listColSheQu = {}
for rowIndex in range(2, maxRowColIndexSheQu + 1):
    valueSheQuName = sheetColIndexSheQu.cell(rowIndex, 1).value
    valueSheQuIndex = sheetColIndexSheQu.cell(rowIndex, 2).value
    if valueSheQuName is not None and valueSheQuName != '':
        listColSheQu[valueSheQuName] = valueSheQuIndex
logger.debug('{}'.format(listColSheQu))

# 打开 模板-月度得分统计.xlsx
# bookResultMuBan = openpyxl.load_workbook(fileResultMuBan, data_only=True)
bookResultMuBan = openpyxl.load_workbook(fileResultMuBan)
sheetResultKeShi = bookResultMuBan['科室']
maxRowResultMuBanKeShi = sheetResultKeShi.max_row
maxColResultMuBanKeShi = sheetResultKeShi.max_column
logger.debug('模板-月度得分统计.xlsx 科室-------行数：{}行    列数：{}列'.format(maxRowResultMuBanKeShi, maxColResultMuBanKeShi))

sheetResultSheQu = bookResultMuBan['社区村']
maxRowResultMuBanSheQu = sheetResultSheQu.max_row
maxColResultMuBanSheQu = sheetResultSheQu.max_column
logger.debug('模板-月度得分统计.xlsx 社区村-------行数：{}行    列数：{}列'.format(maxRowResultMuBanSheQu, maxColResultMuBanSheQu))


chengBan = ChengBanBuMen.ChengBanDirectory()
chengBan.load_cheng_ban_list(fileChengBanBuMen)
listChengBanKeShi = chengBan.get_list_ke_shi()
logger.debug('{}'.format(listChengBanKeShi))
listChengBan = {}
for index in listChengBanKeShi:
    bumenChengJi = buMenChengJi(1)
    bumenChengJi.name = index
    bumenChengJi.fuZeRen = listChengBanKeShi[index].fuZeRen
    bumenChengJi.zhuGuanLingDao = listChengBanKeShi[index].zhuGuanLingDao
    bumenChengJi.baoPianLingDao = listChengBanKeShi[index].baoPianLingDao
    bumenChengJi.suoShuPianQu = listChengBanKeShi[index].suoShuPianQu
    listChengBan[index] = bumenChengJi

for index in listChengBan:
    logger.debug('{} : {}'.format(index, listChengBan[index].fuZeRen))

listChengBanSheQu = chengBan.get_list_she_qu()
logger.debug('{}'.format(listChengBanSheQu))
listSheQuInfo = {}
for index in listChengBanSheQu:
    bumenChengJi = buMenChengJi(0)
    bumenChengJi.name = index
    bumenChengJi.fuZeRen = listChengBanSheQu[index].fuZeRen
    bumenChengJi.zhuGuanLingDao = listChengBanSheQu[index].zhuGuanLingDao
    bumenChengJi.baoPianLingDao = listChengBanSheQu[index].baoPianLingDao
    bumenChengJi.suoShuPianQu = listChengBanSheQu[index].suoShuPianQu
    bumenChengJi.qianRenSuQiuLiang = listChengBanSheQu[index].qianRenSuQiu
    bumenChengJi.fenDang = listChengBanSheQu[index].fenDang
    listChengBan[index] = bumenChengJi

for index in listChengBan:
    logger.debug('{} : {}'.format(index, listChengBan[index].fuZeRen))

sheetJieDan.insert_cols(1, 1)
sheetJieDan.cell(1, 1).value = '是否已处理'
colChengBanJieDan = hanchxl.get_col_index(sheetJieDan, '承办部门')

maxRowJieDan = sheetJieDan.max_row
logger.debug('maxRowJieDan ：{}'.format(maxRowJieDan))
numWeiChuLi = 0
for lineIndexJieDan in range(2, maxRowJieDan + 1):
    valueChengBanJieDan = '{}'.format(sheetJieDan.cell(lineIndexJieDan, colChengBanJieDan).value).strip()
    valueChengBanJieDan = valueChengBanJieDan.replace('(已删除)', '')
    logger.debug('接单 {} {}'.format(lineIndexJieDan, valueChengBanJieDan))
    if valueChengBanJieDan != '':
        if valueChengBanJieDan in listChengBan:
            listChengBan[valueChengBanJieDan].jieDanZongLiang = listChengBan[valueChengBanJieDan].jieDanZongLiang + 1
            if listChengBan[valueChengBanJieDan].isKeShi == 1:
                sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-科室'
            else:
                sheetJieDan.cell(lineIndexJieDan, 1).value = '已处理-社区村'
        else:
            logger.debug('接单  {} 不在承办部门列表'.format(valueChengBanJieDan))
            sheetJieDan.cell(lineIndexJieDan, 1).value = '未找到该承办部门'
            numWeiChuLi = numWeiChuLi + 1
    else:
        logger.debug('接单  {}没有承办部门或标签类型'.format(valueChengBanJieDan))
        sheetJieDan.cell(lineIndexJieDan, 1).value = '没有承办部门或标签类型'
        numWeiChuLi = numWeiChuLi + 1

for index in listChengBan:
    logger.debug('{} 接单量: {}'.format(index, listChengBan[index].jieDanZongLiang))

if numWeiChuLi > 0:
    # 保存未匹配承办部门的表格
    bookJieDan.save(fileJieDanNo)
    logger.debug('保存  {}'.format(fileJieDanNo))


sheetKaoHe.insert_cols(1, 1)
sheetKaoHe.cell(1, 1).value = '是否已处理'

# 处理 考核工单列表
colChengBanIndex = hanchxl.get_col_index(sheetKaoHe, '承办部门')
colLeiXing = hanchxl.get_col_index(sheetKaoHe, '纳入考核')
colLianXi = hanchxl.get_col_index(sheetKaoHe, '是否联系')
colJieJue =hanchxl.get_col_index(sheetKaoHe, '是否解决')
colManYi = hanchxl.get_col_index(sheetKaoHe, '是否满意')
logger.debug('承办部门：{}列  '.format(colChengBanIndex))
logger.debug('纳入考核：{}列'.format(colLeiXing))
logger.debug('联系：{}列'.format(colLianXi))
logger.debug('解决：{}列'.format(colJieJue))
logger.debug('满意：{}列'.format(colManYi))

numWeiChuLiKaoHe = 0
for lineIndex in range(2, maxRowKaoHe + 1):
    valueChengBan = '{}'.format(sheetKaoHe.cell(lineIndex, colChengBanIndex).value).strip().replace('(已删除)', '')
    valueLeiXing = '{}'.format(sheetKaoHe.cell(lineIndex, colLeiXing).value).strip()
    valueLianxi = '{}'.format(sheetKaoHe.cell(lineIndex, colLianXi).value).strip()
    valueJieJue = '{}'.format(sheetKaoHe.cell(lineIndex, colJieJue).value).strip()
    valueManYi = '{}'.format(sheetKaoHe.cell(lineIndex, colManYi).value).strip()
    logger.debug('考核 {}/{} {} {} {} {} {}'.format(lineIndex, maxRowKaoHe, valueChengBan, valueLeiXing, valueLianxi,
                                                    valueJieJue, valueManYi))
    if valueChengBan != '' and valueLeiXing != '':
        if valueLeiXing == '只考核响应率':    # 只考核响应率  工单
            if valueChengBan in listChengBan:
                listChengBan[valueChengBan].kaoHeZongLiang = listChengBan[valueChengBan].kaoHeZongLiang + 1
                listChengBan[valueChengBan].onlyKaoHe = listChengBan[valueChengBan].onlyKaoHe + 1
                if valueLianxi == '联系':
                    listChengBan[valueChengBan].onlyLianXi = listChengBan[valueChengBan].onlyLianXi + 1
                else:
                    listChengBan[valueChengBan].onlyWeiLianXi = listChengBan[valueChengBan].onlyWeiLianXi + 1
                if listChengBan[index].isKeShi == 1:
                    sheetKaoHe.cell(lineIndex, 1).value = '已处理-科室'
                else:
                    sheetKaoHe.cell(lineIndex, 1).value = '已处理-社区村'
            else:
                logger.debug('考核 {}/{} 未找到承办部门'.format(lineIndex, maxRowKaoHe))
                sheetKaoHe.cell(lineIndex, 1).value = '未处理-未找到承办部门'
                numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1
        elif valueLeiXing == '考核三率':  # 考核三率 工单
            if valueChengBan in listChengBan:
                listChengBan[valueChengBan].kaoHeZongLiang = listChengBan[valueChengBan].kaoHeZongLiang + 1
                listChengBan[valueChengBan].sanKaoHe = listChengBan[valueChengBan].sanKaoHe + 1
                if valueLianxi == '联系' or valueLianxi == '已联系' or valueLianxi == '是':
                    listChengBan[valueChengBan].sanLianXi = listChengBan[valueChengBan].sanLianXi + 1
                if valueLianxi == '未联系' or valueLianxi == '否':
                    listChengBan[valueChengBan].sanWeiLianXi = listChengBan[valueChengBan].sanWeiLianXi + 1
                if valueJieJue == '解决' or valueJieJue == '已解决' or valueJieJue == '是':
                    listChengBan[valueChengBan].sanJieJue = listChengBan[valueChengBan].sanJieJue + 1
                if valueJieJue == '未解决' or valueJieJue == '否':
                    listChengBan[valueChengBan].sanWeiJieJue = listChengBan[valueChengBan].sanWeiJieJue + 1
                if valueManYi == '满意' or valueManYi == '非常满意':
                    listChengBan[valueChengBan].sanManYi = listChengBan[valueChengBan].sanManYi + 1
                if valueManYi == '基本满意':
                    listChengBan[valueChengBan].sanJiBenManYi = listChengBan[valueChengBan].sanJiBenManYi + 1
                if valueManYi == '不满意' or valueManYi == '非常不满意':
                    listChengBan[valueChengBan].sanBuManYi = listChengBan[valueChengBan].sanBuManYi + 1
                if listChengBan[index].isKeShi == 1:
                    sheetKaoHe.cell(lineIndex, 1).value = '已处理-科室'
                else:
                    sheetKaoHe.cell(lineIndex, 1).value = '已处理-社区村'
            else:
                logger.debug('考核 {}/{} 未找到承办部门'.format(lineIndex, maxRowKaoHe))
                sheetKaoHe.cell(lineIndex, 1).value = '未处理-未找到承办部门'
                numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1

if numWeiChuLiKaoHe > 0:
    # 保存未匹配承办部门的表格
    bookKaoHe.save(fileKaoHeNo)
    logger.debug('保存  {}'.format(fileKaoHeNo))

listDeFen = {}
listAllNone = []        # 接单量 和 考核量 均为0
listOnlyJieDan = []        # 考核量 为0
for index in listChengBan:
    logger.debug('{} 接单量: {}'.format(index, listChengBan[index].jieDanZongLiang))
    # 仅考核联系率 联系率
    if listChengBan[index].onlyKaoHe > 0:
        listChengBan[index].onlyLvLianXi = round(listChengBan[index].onlyLianXi * 100 / listChengBan[index].onlyKaoHe)
    # 考核三率 联系率 解决率 满意率
    if listChengBan[index].sanKaoHe > 0:
        listChengBan[index].sanLvLianXi = round(listChengBan[index].sanLianXi * 100 / listChengBan[index].sanKaoHe)
        listChengBan[index].sanLvJieJue = round(listChengBan[index].sanJieJue * 100 / listChengBan[index].sanKaoHe)
        if listChengBan[index].isKeShi == 1:
            listChengBan[index].sanLvManYi = round(
                (listChengBan[index].sanManYi + listChengBan[index].sanJiBenManYi * 0.8) * 100 / listChengBan[
                    index].sanKaoHe)
        else:
            listChengBan[index].sanLvManYi = round(
                (listChengBan[index].sanManYi + listChengBan[index].sanJiBenManYi) * 100 / listChengBan[index].sanKaoHe)
    # 平均联系率
    if listChengBan[index].onlyLvLianXi is None:
        if listChengBan[index].sanLvLianXi is not None:
            listChengBan[index].avgLvLianXi = listChengBan[index].sanLvLianXi
    else:
        if listChengBan[index].sanLvLianXi is None:
            listChengBan[index].avgLvLianXi = listChengBan[index].onlyLvLianXi
        else:
            listChengBan[index].avgLvLianXi = round(
                (listChengBan[index].onlyLvLianXi + listChengBan[index].sanLvLianXi) / 2)
    # 平均解决率
    if listChengBan[index].sanLvJieJue is not None:
        listChengBan[index].avgLvJieJue = listChengBan[index].sanLvJieJue
    # 平均满意率
    if listChengBan[index].sanLvManYi is not None:
        listChengBan[index].avgLvManYi = listChengBan[index].sanLvManYi

    # 社区村计算 响应率得分  解决率得分  满意率得分  工单量得分 得分
    if listChengBan[index].isKeShi != 1:
        # 响应率得分
        if listChengBan[index].onlyLianXi + listChengBan[index].sanLianXi == listChengBan[index].kaoHeZongLiang:
            listChengBan[index].fenLianXi = 5
        else:
            listChengBan[index].fenLianXi = 0
        # 解决率得分
        if listChengBan[index].avgLvJieJue is not None:
            listChengBan[index].fenJieJue = round(listChengBan[index].avgLvJieJue * 20 / 100, 2)
        else:
            listChengBan[index].fenJieJue = 20
        # 满意率得分
        if listChengBan[index].avgLvManYi is not None:
            listChengBan[index].fenManYi = round(listChengBan[index].avgLvManYi * 15 / 100, 2)
        else:
            listChengBan[index].fenManYi = 15
        # 工单量得分
        if listChengBan[index].jieDanZongLiang == 0:
            listChengBan[index].fenGongDan = 10
        else:
            logger.debug('{} {} {}'.format(listChengBan[index].name, listChengBan[index].jieDanZongLiang, listChengBan[index].qianRenSuQiuLiang))
            listChengBan[index].fenGongDan = 5 - 0.2 * (
                        listChengBan[index].jieDanZongLiang - listChengBan[index].qianRenSuQiuLiang)
            if listChengBan[index].fenGongDan > 10:
                listChengBan[index].fenGongDan = 10
            if listChengBan[index].fenGongDan < 0:
                listChengBan[index].fenGongDan = 0
        # 得分
        listChengBan[index].deFen = listChengBan[index].fenLianXi + listChengBan[index].fenJieJue + listChengBan[
            index].fenManYi + listChengBan[index].fenGongDan
    else:
        if listChengBan[index].avgLvJieJue is not None:
            listChengBan[index].deFen = listChengBan[index].avgLvJieJue * 0.5
        else:
            listChengBan[index].deFen = 50
        if listChengBan[index].avgLvManYi is not None:
            listChengBan[index].deFen = listChengBan[index].deFen + listChengBan[index].avgLvManYi * 0.4
        else:
            listChengBan[index].deFen = listChengBan[index].deFen + 40
        if listChengBan[index].avgLvLianXi is not None:
            listChengBan[index].deFen = listChengBan[index].deFen + listChengBan[index].avgLvLianXi * 0.1
        else:
            listChengBan[index].deFen = listChengBan[index].deFen + 10
    listDeFen[listChengBan[index].name] = listChengBan[index].deFen
    logger.debug('{} 接单：{} 考核：{} 仅考核响应率：响应率：{} 三率：响应率：{}'.format(index, listChengBan[index].jieDanZongLiang,
                                             listChengBan[index].kaoHeZongLiang, listChengBan[index].onlyLvLianXi,
                                                                                   listChengBan[index].sanLvLianXi))
    if listChengBan[index].jieDanZongLiang == 0 and listChengBan[index].kaoHeZongLiang == 0:
        listAllNone.append(listChengBan[index].name)
    if listChengBan[index].jieDanZongLiang != 0 and listChengBan[index].kaoHeZongLiang == 0:
        listOnlyJieDan.append(listChengBan[index].name)

listDeFen = sorted(listDeFen.items(), key=lambda kv: kv[1], reverse=True)


def inset_not_none_info(is_ke_shi, title_name, insert_row, insert_value):
    if insert_value is not None:
        if title_name in listColKeshi or title_name in listColSheQu:
            if is_ke_shi == 1:
                sheetResultKeShi.cell(insert_row, listColKeshi[title_name]).value = insert_value
            else:
                sheetResultSheQu.cell(insert_row, listColSheQu[title_name]).value = insert_value

lastDeFenKeshi = 0
lastPaimMingKeshi = 1
lastDefenSheQu = 0
lastPaimMingSheQu = 1
# 承办部门信息插入到结果表
def insert_into_excel(bu_men_info, insert_row):
    global lastDeFenKeshi
    global lastPaimMingKeshi
    global lastDefenSheQu
    global lastPaimMingSheQu
    if bu_men_info.isKeShi == 1:
        logger.debug('科室 {} {} {}'.format(bu_men_info.name, insert_row, listColKeshi['接单部门']))
        inset_not_none_info(1, '接单部门', insert_row, bu_men_info.name)
        inset_not_none_info(1, '接单部门', insert_row, bu_men_info.name)
        if bu_men_info.jieDanZongLiang > 0:
            inset_not_none_info(1, '接单总量', insert_row, bu_men_info.jieDanZongLiang)
        if bu_men_info.kaoHeZongLiang> 0:
            inset_not_none_info(1, '考核总量', insert_row, bu_men_info.kaoHeZongLiang)
        if bu_men_info.onlyKaoHe > 0:
            inset_not_none_info(1, '仅响应率-考核', insert_row, bu_men_info.onlyKaoHe)
            inset_not_none_info(1, '仅响应率-联系', insert_row, bu_men_info.onlyLianXi)
            inset_not_none_info(1, '仅响应率-未联系', insert_row, bu_men_info.onlyWeiLianXi)
            inset_not_none_info(1, '仅响应率-响应率', insert_row, bu_men_info.onlyLvLianXi)
        if bu_men_info.sanKaoHe > 0:
            inset_not_none_info(1, '三率-考核', insert_row, bu_men_info.sanKaoHe)
            inset_not_none_info(1, '三率-联系', insert_row, bu_men_info.sanLianXi)
            inset_not_none_info(1, '三率-未联系', insert_row, bu_men_info.sanWeiLianXi)
            inset_not_none_info(1, '三率-解决', insert_row, bu_men_info.sanJieJue)
            inset_not_none_info(1, '三率-未解决', insert_row, bu_men_info.sanWeiJieJue)
            inset_not_none_info(1, '三率-满意', insert_row, bu_men_info.sanManYi)
            inset_not_none_info(1, '三率-基本满意', insert_row, bu_men_info.sanJiBenManYi)
            inset_not_none_info(1, '三率-不满意', insert_row, bu_men_info.sanBuManYi)
            inset_not_none_info(1, '三率-响应率', insert_row, bu_men_info.sanLvLianXi)
            inset_not_none_info(1, '三率-解决率', insert_row, bu_men_info.sanLvJieJue)
            inset_not_none_info(1, '三率-满意率', insert_row, bu_men_info.sanLvManYi)
        inset_not_none_info(1, '平均响应率', insert_row, bu_men_info.avgLvLianXi)
        inset_not_none_info(1, '平均解决率', insert_row, bu_men_info.avgLvJieJue)
        inset_not_none_info(1, '平均满意率', insert_row, bu_men_info.avgLvManYi)
        inset_not_none_info(1, '平均满意率', insert_row, bu_men_info.avgLvManYi)
        inset_not_none_info(1, '得分', insert_row, bu_men_info.deFen)
        if bu_men_info.deFen == lastDeFenKeshi:
            inset_not_none_info(1, '排名', insert_row, lastPaimMingKeshi)
        else:
            lastPaimMingKeshi = insert_row - 3
            inset_not_none_info(1, '排名', insert_row, lastPaimMingKeshi)
        lastDeFenKeshi = bu_men_info.deFen
        inset_not_none_info(1, '主管领导', insert_row, bu_men_info.zhuGuanLingDao)
    else:
        logger.debug('社区村 {} {} {}'.format(bu_men_info.name, insert_row, listColSheQu['接单部门']))
        inset_not_none_info(0, '接单部门', insert_row, bu_men_info.name)
        inset_not_none_info(0, '千人诉求量', insert_row, bu_men_info.qianRenSuQiuLiang)
        if bu_men_info.jieDanZongLiang > 0:
            inset_not_none_info(0, '接单总量', insert_row, bu_men_info.jieDanZongLiang)
        if bu_men_info.kaoHeZongLiang > 0:
            inset_not_none_info(0, '考核总量', insert_row, bu_men_info.kaoHeZongLiang)
        if bu_men_info.onlyKaoHe > 0:
            inset_not_none_info(0, '仅响应率-考核', insert_row, bu_men_info.onlyKaoHe)
            inset_not_none_info(0, '仅响应率-联系', insert_row, bu_men_info.onlyLianXi)
            inset_not_none_info(0, '仅响应率-未联系', insert_row, bu_men_info.onlyWeiLianXi)
            inset_not_none_info(0, '仅响应率-响应率', insert_row, bu_men_info.onlyLvLianXi)
        if bu_men_info.sanKaoHe > 0:
            inset_not_none_info(0, '三率-考核', insert_row, bu_men_info.sanKaoHe)
            inset_not_none_info(0, '三率-联系', insert_row, bu_men_info.sanLianXi)
            inset_not_none_info(0, '三率-未联系', insert_row, bu_men_info.sanWeiLianXi)
            inset_not_none_info(0, '三率-解决', insert_row, bu_men_info.sanJieJue)
            inset_not_none_info(0, '三率-未解决', insert_row, bu_men_info.sanWeiJieJue)
            inset_not_none_info(0, '三率-满意', insert_row, bu_men_info.sanManYi)
            inset_not_none_info(0, '三率-基本满意', insert_row, bu_men_info.sanJiBenManYi)
            inset_not_none_info(0, '三率-不满意', insert_row, bu_men_info.sanBuManYi)
            inset_not_none_info(0, '三率-响应率', insert_row, bu_men_info.sanLvLianXi)
            inset_not_none_info(0, '三率-解决率', insert_row, bu_men_info.sanLvJieJue)
            inset_not_none_info(0, '三率-满意率', insert_row, bu_men_info.sanLvManYi)
        inset_not_none_info(0, '平均响应率', insert_row, bu_men_info.avgLvLianXi)
        inset_not_none_info(0, '平均解决率', insert_row, bu_men_info.avgLvJieJue)
        inset_not_none_info(0, '平均满意率', insert_row, bu_men_info.avgLvManYi)
        inset_not_none_info(0, '响应率得分', insert_row, bu_men_info.fenLianXi)
        inset_not_none_info(0, '解决率得分', insert_row, bu_men_info.fenJieJue)
        inset_not_none_info(0, '满意率得分', insert_row, bu_men_info.fenManYi)
        inset_not_none_info(0, '工单量得分', insert_row, bu_men_info.fenGongDan)
        inset_not_none_info(0, '所属片区', insert_row, bu_men_info.suoShuPianQu)
        inset_not_none_info(0, '分档', insert_row, bu_men_info.fenDang)


logger.debug("{}".format(listAllNone))
insetKeShi = 4
insertSheQu = 4
for index in listDeFen:
    logger.debug("{}".format(index))
    if index[0] in listAllNone:
        if listChengBan[index[0]].isKeShi == 1:
            insert_into_excel(listChengBan[index[0]], insetKeShi)
            insetKeShi = insetKeShi + 1
        else:
            insert_into_excel(listChengBan[index[0]], insertSheQu)
            insertSheQu = insertSheQu + 1

for index in listDeFen:
    logger.debug("{}".format(index))
    if index[0] in listOnlyJieDan:
        if listChengBan[index[0]].isKeShi == 1:
            insert_into_excel(listChengBan[index[0]], insetKeShi)
            insetKeShi = insetKeShi + 1
        else:
            insert_into_excel(listChengBan[index[0]], insertSheQu)
            insertSheQu = insertSheQu + 1

for index in listDeFen:
    if index[0] not in listAllNone and index[0] not in listOnlyJieDan:
        if listChengBan[index[0]].isKeShi == 1:
            insert_into_excel(listChengBan[index[0]], insetKeShi)
            insetKeShi = insetKeShi + 1
        else:
            insert_into_excel(listChengBan[index[0]], insertSheQu)
            insertSheQu = insertSheQu + 1

bookResultMuBan.save(fileResultSave)




